<!DOCTYPE html>
<html lang="en">

<!-- Head tag -->
<head><meta name="generator" content="Hexo 3.9.0">

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!--Description-->
    
        <meta name="description" content="高负载高并发环境下，数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器负载，如此多的数据库连接操作，数据库必然会崩溃，数据库如果宕机的话，后果更是不堪设想。这时候，我们会考虑如何减少数据库的连接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：redis,如">
    

    <!--Author-->
    
        <meta name="author" content="ck">
    

    <!--Open Graph Title-->
    
        <meta property="og:title" content="关于mysql主从热备，实现数据同步的理解">
    

    <!--Open Graph Description-->
    

    <!--Open Graph Site Name-->
    <meta property="og:site_name" content="CK&#39;s blogs">

    <!--Type page-->
    
        <meta property="og:type" content="article">
    

    <!--Page Cover-->
    

    <meta name="twitter:card" content="summary">
    

    <!-- Title -->
    
    <title>关于mysql主从热备，实现数据同步的理解 - CK&#39;s blogs</title>

    <!-- Bootstrap Core CSS -->
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.2/css/bootstrap.min.css" integrity="sha384-y3tfxAZXuh4HwSYylfB+J125MxIs6mR5FOHamPBG064zB+AFeWH94NdvaCBm8qnd" crossorigin="anonymous">

    <!-- Custom Fonts -->
    <link href="//maxcdn.bootstrapcdn.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet" type="text/css">

    <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
        <script src="//oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
        <script src="//oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
    <![endif]-->

    <!-- Gallery -->
    <link href="//cdnjs.cloudflare.com/ajax/libs/featherlight/1.3.5/featherlight.min.css" type="text/css" rel="stylesheet">

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/blog/css/style.css">

    <!-- Google Analytics -->
    


</head>


<body>

<div class="bg-gradient"></div>
<div class="bg-pattern"></div>

<!-- Menu -->
<!--Menu Links and Overlay-->
<div class="menu-bg">
    <div class="menu-container">
        <ul>
            
            <li class="menu-item">
                <a href="/blog/">
                    Home
                </a>
            </li>
            
            <li class="menu-item">
                <a href="/blog/archives">
                    Archives
                </a>
            </li>
            
            <li class="menu-item">
                <a href="/blog/2019/09/04/本人简历/">
                    About
                </a>
            </li>
            
            <li class="menu-item">
                <a href="/blog/tags">
                    Tags
                </a>
            </li>
            
            <li class="menu-item">
                <a href="/blog/categories">
                    Categories
                </a>
            </li>
            
            <li class="menu-item">
                <a href="/blog/contact.html">
                    Contact
                </a>
            </li>
            
        </ul>
    </div>
</div>

<!--Hamburger Icon-->
<nav>
    <a href="#menu"></a>
</nav>

<div class="container">

    <!-- Main Content -->
    <div class="row">
    <div class="col-sm-12">

        <!--Title and Logo-->
        <header>
    <div class="logo">
        <a href="/blog/"><i class="logo-icon fa fa-cube" aria-hidden="true"></i></a>
        
    </div>
</header>

        <section class="main">
            
<div class="post">

    <div class="post-header">
        <h1 class="title">
            <a href="/blog/2019/09/19/关于mysql主从热备，实现数据同步的理解/">
                关于mysql主从热备，实现数据同步的理解
            </a>
        </h1>
        <div class="post-info">
            
                <span class="date">2019-09-19</span>
            
            
            
        </div>
    </div>

    <div class="content">

        <!-- Gallery -->
        

        <!-- Post Content -->
        <p>高负载高并发环境下，数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器负载，如此多的数据库连接操作，数据库必然会崩溃，数据库如果宕机的话，后果更是不堪设想。这时候，我们会考虑如何减少数据库的连接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：redis,如果资金丰厚的话，必然会想到架设MySQL服务集群，来分担主数据库的压力。今天我们利用MySQL主从配置，实现读写分离，减轻数据库压力。</p>
<p>MySQL主从同步的<strong>原理</strong>很简单：</p>
<p>从库生成两个线程，一个I/O线程，一个SQL线程；</p>
<p>I/O 线程，去请求主库 的binlog（二进制日志），并将得到的binlog日志写入到relay log（中继日志） 文件中；主库会生成一个 log dump 线程，用来给从库I/O线程传binlog；</p>
<p>SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。</p>
<p><strong>图解</strong>：</p>


<h3 id="关于binlog日志"><a href="#关于binlog日志" class="headerlink" title="关于binlog日志"></a>关于binlog日志</h3><p>binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)</p>
<p>binlog索引文件mysql-bin.index。如官方文档中所写，binlog格式如下：</p>
<p>binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfe ‘b’’i’’n’。</p>
<p>binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。</p>
<p>header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等。</p>
<p>data部分提供的是针对该event的具体信息，如具体数据的修改。</p>

    </div>

    

    
        <div class="post-tags">
            <i class="fa fa-tags" aria-hidden="true"></i>
            <a href="/blog/tags/MySQL/">#MySQL</a> <a href="/blog/tags/主从热备/">#主从热备</a> <a href="/blog/tags/主从数据同步/">#主从数据同步</a>
        </div>
    

    <!-- Comments -->
    

</div>
        </section>

    </div>
</div>


</div>

<!-- Footer -->
<div class="push"></div>

<footer class="footer-content">
    <div class="container">
        <div class="row">
            <div class="col-xs-12 col-sm-12 col-md-6 col-lg-6 footer-about">
                <h2>About</h2>
                <p>
                    This theme was developed by <a href="https://github.com/klugjo">Jonathan Klughertz</a>. The source code is available on Github. Create Websites. Make Magic.
                </p>
            </div>
            
    <div class="col-xs-6 col-sm-6 col-md-3 col-lg-3 recent-posts">
        <h2>Recent Posts</h2>
        <ul>
            
            <li>
                <a class="footer-post" href="/blog/2019/09/19/关于mysql主从热备，实现数据同步的理解/">关于mysql主从热备，实现数据同步的理解</a>
            </li>
            
            <li>
                <a class="footer-post" href="/blog/2019/09/18/关于正向代理和反向代理的理解/">关于正向代理和反向代理的理解</a>
            </li>
            
            <li>
                <a class="footer-post" href="/blog/2019/09/15/你真的了解Python内存管理机制吗？/">你真的了解Python内存管理机制吗？</a>
            </li>
            
            <li>
                <a class="footer-post" href="/blog/2019/09/13/三次握手四次挥手详解/">三次握手四次挥手详解</a>
            </li>
            
        </ul>
    </div>



            
        </div>
        <div class="row">
            <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
                <ul class="list-inline footer-social-icons">
                    
                    <li class="list-inline-item">
                        <a href="https://github.com/klugjo/hexo-theme-alpha-dust">
                            <span class="footer-icon-container">
                                <i class="fa fa-github"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://twitter.com/?lang=en">
                            <span class="footer-icon-container">
                                <i class="fa fa-twitter"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://www.facebook.com/">
                            <span class="footer-icon-container">
                                <i class="fa fa-facebook"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://www.instagram.com/">
                            <span class="footer-icon-container">
                                <i class="fa fa-instagram"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://dribbble.com/">
                            <span class="footer-icon-container">
                                <i class="fa fa-dribbble"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://plus.google.com/">
                            <span class="footer-icon-container">
                                <i class="fa fa-google-plus"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://www.behance.net/">
                            <span class="footer-icon-container">
                                <i class="fa fa-behance"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="https://500px.com/">
                            <span class="footer-icon-container">
                                <i class="fa fa-500px"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="mailto:test@example.com">
                            <span class="footer-icon-container">
                                <i class="fa fa-envelope-o"></i>
                            </span>
                        </a>
                    </li>
                    
                    
                    <li class="list-inline-item">
                        <a href="\#">
                            <span class="footer-icon-container">
                                <i class="fa fa-rss"></i>
                            </span>
                        </a>
                    </li>
                    
                </ul>
            </div>
        </div>
        <div class="row">
            <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
                <div class="footer-copyright">
                    @Untitled. All right reserved | Design & Hexo <a href="http://www.codeblocq.com/">Jonathan Klughertz</a>
                </div>
            </div>
        </div>
    </div>
</footer>

<!-- After footer scripts -->

<!-- jQuery -->
<script src="//code.jquery.com/jquery-2.1.4.min.js"></script>

<!-- Tween Max -->
<script src="//cdnjs.cloudflare.com/ajax/libs/gsap/1.18.5/TweenMax.min.js"></script>

<!-- Gallery -->
<script src="//cdnjs.cloudflare.com/ajax/libs/featherlight/1.3.5/featherlight.min.js" type="text/javascript" charset="utf-8"></script>

<!-- Custom JavaScript -->
<script src="/blog/js/main.js"></script>

<!-- Disqus Comments -->



</body>

</html>